﻿-- Tìm kiếm học viên
-- EXEC uspLaySinhVien @GioiTinh = ''
ALTER PROC uspLaySinhVien
	@MaSV NVARCHAR(50) =null,
	@TenSV NVARCHAR(50) =null,
	@NgaySinh DATE = null,
	@KhoaHoc INT = null,
	@GioiTinh VARCHAR(1) = null,	
	@Lop NVARCHAR(20) = null,
	@Nganh NVARCHAR(50) = null
AS
BEGIN
	-- Xóa ký tự trắng
	IF @MaSV = '' SET @MaSV = NULL;
	IF @TenSV = '' SET @TenSV = NULL;
	IF @KhoaHoc = '' SET @KhoaHoc = NULL;
	IF @GioiTinh = '' SET @GioiTinh = NULL;
	IF @Lop = '' SET @Lop = NULL;
	IF @Nganh = '' SET @Nganh = NULL

	-- Tim kiem
	SELECT MaSV, 
		TenSV, 
		Nganh, 
		NgaySinh, 
		CASE GioiTinh 
			WHEN 0 THEN N'Nam' 
			ELSE N'Nữ' 
			END 
		AS 'GioiTinh', 
		Lop, 
		KH.TenKhoaHoc AS 'KhoaHoc'
	FROM HocSinh AS HS WITH (NOLOCK)
	JOIN KhoaHoc AS KH WITH (NOLOCK) ON HS.KhoaHoc=KH.ID 
	WHERE (MaSV = @MaSV or @MaSV is null) 
	AND (TenSV LIKE '%'+ @TenSV +'%' or @TenSV is null)
	AND (Nganh LIKE '%'+ @Nganh +'%' or @Nganh is null)
	AND (NgaySinh = @NgaySinh or @NgaySinh is null)
	AND (GioiTinh = @GioiTinh or @GioiTinh is null)
	AND (Lop = @Lop or @Lop is null)
	AND (ID = @KhoaHoc or @KhoaHoc is null)
END
GO